<?php

require_once("lib/exception/duplicate_instance_exception.php");
require_once("lib/exception/internal_error_exception.php");
require_once("lib/exception/instance_not_found_exception.php");
require_once("lib/exception/sql_exception.php");

require_once("util/connection.php");

require_once("model/article/to/article_to.php");
require_once("model/article/dao/article_dao.php");


class Mysql_article_dao extends Article_dao {
	
	public function create($article_to) {
		if ($this->exist($article_to->get_number())) {
			throw new Duplicate_instance_exception(get_class($this));
		}
		try {	
			$link = Connection::singleton()->transaction_begin();
			
			$title = mysql_real_escape_string($article_to->get_title());
			$autor_login = mysql_real_escape_string($article_to->get_autor_login());
			$date = mysql_real_escape_string($article_to->get_date());
			$state = mysql_real_escape_string($article_to->get_state());
			$view = $article_to->get_view();			
			
			$result = mysql_query("insert into article 
									(title, autor_login, date, state, view)
									values ('$title', '$autor_login', 
									'$date', '$state', '$view')", $link);
			
			if (!$result || (mysql_affected_rows($link) != 1) 
					|| (($number = mysql_insert_id($link)) == 0)) 
				throw new Sql_exception("Not row inserted in " . get_class($this));
			
												
			Connection::singleton()->transaction_commit($link);
			$article_to = new Article_to($number, $title, $autor_login, $date, $state, $view);
			return $article_to;
			
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
		catch (Exception $e) {
			Connection::singleton()->transaction_rollback($link);
			throw new Exception("Internal error exception --> " . $e);
		}
	}
		
	public function exist($number){
		try {	
			$link = Connection::singleton()->transaction_begin();

			$result = mysql_query("select * from article where number='$number'", $link);
											
			if (!$result || (mysql_num_rows($result) != 1)) {
				Connection::singleton()->transaction_commit($link);
				return false;
			}
			
			Connection::singleton()->transaction_commit($link);
			return true;
			
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function find_by_number_article($number){
		try {	
			$link = Connection::singleton()->transaction_begin();

			$result = mysql_query("select * from article where number='$number'", $link);
			
			if (mysql_num_rows($result) == 0) 
				throw new Instance_not_found_exception(get_class($this));
			
			if (mysql_num_rows($result) != 1) {
				throw new Sql_exception(get_class($this));
			}
			
			$aux = mysql_fetch_assoc($result);
			Connection::singleton()->transaction_commit($link);			
			
			return new Article_to($aux['number'], $aux['title'], $aux['autor_login'], $aux['date'],
									$aux['state'], $aux['view']);
			
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e) {
			Connection::singleton()->transaction_rollback($link);
			throw new Exception("Internal error exception --> " . $e);
		}
	}

	private function find_by_keywords($query, $start_index, $count) {
		try {	
			$link = Connection::singleton()->transaction_begin();

			$result = mysql_query($query, $link);
			
			if (mysql_num_rows($result) == 0) {
				throw new Instance_not_found_exception(get_class($this));
			}
			
			$article_list = NULL;
			$i = 0;			
			
			if ($start_index < (mysql_num_rows($result)) && (mysql_data_seek($result,$start_index))) {				
				while (($aux = mysql_fetch_assoc($result)) && ($i <= $count))
					$article_list[$i++] = new Article_to($aux['number'], $aux['title'], $aux['autor_login'], $aux['date'],
															$aux['state'], $aux['view']);
			}
			
			Connection::singleton()->transaction_commit($link);						
			return $article_list;
			
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}	
	}
	
	public function find_by_keywords_title_and($keywords, $start_index, $count) {
		try {
			$query = "select * from article where (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "title like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "title like '%$keyword%')
								and state = 'open'
								group by title
								order by date";
			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}	
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_or($keywords, $start_index, $count) {
		try {
			$query = "select * from article where (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "title like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "title like '%$keyword%')
								and state = 'open'
								group by title
								order by date";
			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_description_and($keywords, $start_index, $count) {
		try {
			$query = "select a.number, a.title, a.autor_login, a.date, a.state, a.view
						from article as a, article_description as d where 
						a.number=d.article_number and ((";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "a.title like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "a.title like '%$keyword%') or (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "d.content like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "d.content like '%$keyword%'))
								and a.state = 'open'
								and d.state = 'open'
								group by a.title
								order by a.date";
			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_description_or($keywords, $start_index, $count) {
		try {
			$query = "select a.number, a.title, a.autor_login, a.date, a.state, a.view
						from article as a, article_description as d where 
						a.number=d.article_number and (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "a.title like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "a.title like '%$keyword%' or ";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "d.content like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "d.content like '%$keyword%')
								and a.state = 'open'
								and d.state = 'open'
								group by a.title
								order by a.date";

			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_category_and($keywords, $start_index, $count) {
		try {
			$query = "select a.number, a.title, a.autor_login, a.date, a.state, a.view
						from article as a, article_article_category as ac, article_category as c where ";
			$query = $query . "ac.article_number = a.number
								and ac.article_category_number = c.number
								and a.state = 'open' and ((";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "a.title like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "a.title like '%$keyword%') or (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "c.title like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "c.title like '%$keyword%'))
								group by a.title								
								order by a.date";
			
			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_category_or($keywords, $start_index, $count) {
		try {
			$query = "select a.number, a.title, a.autor_login, a.date, a.state, a.view
						from article as a, article_article_category as ac, article_category as c where ";
			$query = $query . "ac.article_number = a.number
								and ac.article_category_number = c.number
								and a.state = 'open' and (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "a.title like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "a.title like '%$keyword%' or ";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "c.title like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "c.title like '%$keyword%')
								group by a.title								
								order by a.date";

			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_description_category_and($keywords, $start_index, $count) {
		try {
			$query = "select a.number, a.title, a.autor_login, a.date, a.state, a.view
						from article as a, article_article_category as ac, article_category as c, 
						article_description as d where ";
			$query = $query . "ac.article_number = a.number
								and ac.article_category_number = c.number
								and d.article_number = a.number
								and a.state = 'open' and ((";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "a.title like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "a.title like '%$keyword%') or (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "c.title like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "c.title like '%$keyword%') or (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "d.content like '%$keyword%' and ";
			}
			$keyword = $keywords[$i];
			$query = $query . "d.content like '%$keyword%'))
								group by a.title								
								order by a.date";
			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_by_keywords_title_description_category_or($keywords, $start_index, $count) {
		try {
			$query = "select a.number, a.title, a.autor_login, a.date, a.state, a.view
						from article as a, article_article_category as ac, article_category as c, 
						article_description as d where ";
			$query = $query . "ac.article_number = a.number
								and ac.article_category_number = c.number
								and d.article_number = a.number
								and a.state = 'open' and ((";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "a.title like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "a.title like '%$keyword%') or (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "c.title like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "c.title like '%$keyword%') or (";
			for($i=0;$i<(count($keywords)-1);$i++) {
				$keyword = $keywords[$i];
				$query = $query . "d.content like '%$keyword%' or ";
			}
			$keyword = $keywords[$i];
			$query = $query . "d.content like '%$keyword%'))
								group by a.title								
								order by a.date";
			return $this->find_by_keywords($query, $start_index, $count);
			
		}
		catch (Instance_not_found_exception $e){
			throw $e;
		}
		catch (Internal_error_exception $e){
			throw $e;
		}
		catch (Exception $e){
			throw $e;
		}	
	}
	
	public function find_most_recent($start_index, $count) {
		try {	
			$link = Connection::singleton()->transaction_begin();

			$result = mysql_query("select * from article 
									where state = 'open'
									order by date desc", $link);

			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
			
			$article_list = NULL;
			$i = 0;			
			
			if ($start_index < (mysql_num_rows($result)) && (mysql_data_seek($result,$start_index))) {				
				while (($aux = mysql_fetch_assoc($result)) && ($i <= $count))
					$article_list[$i++] = new Article_to($aux['number'], $aux['title'], $aux['autor_login'], $aux['date'],
															$aux['state'], $aux['view']);
			}
			
			Connection::singleton()->transaction_commit($link);						
			return $article_list;
			
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function find_most_viewed($start_index, $count) {
		try {	
			$link = Connection::singleton()->transaction_begin();

			$result = mysql_query("select * from article 
									where state = 'open'
									order by view desc", $link);

			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
			
			$article_list = NULL;
			$i = 0;			
			if ($start_index < (mysql_num_rows($result)) && (mysql_data_seek($result,$start_index))) {				
				while (($aux = mysql_fetch_assoc($result)) && ($i <= $count))
					$article_list[$i++] = new Article_to($aux['number'], $aux['title'], $aux['autor_login'], $aux['date'],
															$aux['state'], $aux['view']);
			}
			Connection::singleton()->transaction_commit($link);						
			return $article_list;
			
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function count_articles_by_login($login) {
		try {	
			$link = Connection::singleton()->transaction_begin();

			$login = mysql_real_escape_string($login);
			
			$result = mysql_query("select COUNT(number) as count from article 
									where autor_login like '$login'", $link);
									
			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
			
			$aux = mysql_fetch_assoc($result);
			
			Connection::singleton()->transaction_commit($link);
									
			return $aux['count'];
						
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function update($article_to){
		 try {	
			$link = Connection::singleton()->transaction_begin();

			$number = $article_to->get_number();
			$title = mysql_real_escape_string($article_to->get_title());
			$state = mysql_real_escape_string($article_to->get_state());
			$view = $article_to->get_view();
			
			$result = mysql_query("update article set title='$title',
									state='$state', view='$view'
									where number='$number'", $link);
																	
			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
			
			if (mysql_affected_rows($link) != 1) 
				throw new Sql_exception("Not row updated in " . get_class($this));
			
			
			Connection::singleton()->transaction_commit($link);
			
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);		
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}	
	}
    
	public function remove($number){
		 try {	
			$link = Connection::singleton()->transaction_begin();

			$result = mysql_query("delete from article where number='$number'", $link);

			if (mysql_affected_rows($link) == 0) 
				throw new Instance_not_found_exception(get_class($this));
			
			if (mysql_affected_rows($link) != 1) 
				throw new Sql_exception("Not row removed in " . get_class($this));
			
			Connection::singleton()->transaction_commit($link);
			
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);		
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}	
	}
	
}
	      

?>